﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Dailylist]
	(
		@CompanyId varchar(500),
		@StartDate nchar(10),
		@EndDate nchar(10),
		@StartIndex int,
		@EndIndex int
	)
AS
BEGIN
	DECLARE @strSql VARCHAR(4000),@strSql1 VARCHAR(4000)
	--declare @TempSca table(d_str DATETIME )
	--set @strDate=CONVERT(DATETIME, @StartDate)
	--WHILE @strDate<=CONVERT(DATETIME,@EndDate)
	--BEGIN
	--	INSERT INTO @TempSca(d_str) VALUEs(@strDate)
	--	set @strDate=DATEADD(d,1, @strDate)
	--END
	
	set @strSql=''
	if @CompanyId<>'0'
	set @strSql='CompanyId in ('+@CompanyId+') and '
	
	
	
	SET @strSql='	
	
	DECLARE @strDate datetime,@srq datetime,@erq datetime
	declare @TempSca table(d_str DATETIME )
	set @strDate=CONVERT(DATETIME, '''+@StartDate+''')
	WHILE @strDate<=CONVERT(DATETIME,'''+@EndDate+''')
	BEGIN
		INSERT INTO @TempSca(d_str) VALUEs(@strDate)
		set @strDate=DATEADD(d,1, @strDate)
	END
	
	
	set @srq= convert(datetime, '''+@StartDate+' 00:00:00'')
	set @erq= convert(datetime,'''+@EndDate+' 23:59:59'')
	
	
	;WITH list As 
	(
		
		Select 
	Id
	, CompanyId
	,CompanyName
	,ISNULL(IBA,0) As IBA
	,ISNULL(IBB,0) As IBB
	,ISNULL(IBC,0) As IBC
	,(Select Ename From Employee Where UserName=Statistics_Financial.CreateUser) As CreateEName
	,CreateDate
	,(select sort from zzjg where companyid=Statistics_Financial.companyid) as sort
	From Statistics_Financial Where '+@strSql+' 
	
	 CreateDate between @srq and @erq
	),
	'
	SET @strSql1='	
	  list1 as  (
	         select ROW_NUMBER() OVER 	(ORDER BY CreateDate,
	        sort ASC,companyid )AS Row,*
	       
	       
	        
	        
			from (
				Select * From list 		
				UNION				
				SELECT 0,id,companyname,0,0,0,'''',d_str,''500'' FROM @TempSca,Company  WHERE id NOT IN (SELECT companyid FROM list  WHERE CONVERT(VARCHAR(10),CreateDate,120)=CONVERT(VARCHAR(10),d_str,120)) 
				
				 AND  id in ('+@CompanyId+')) as nb 
	)
	--) as nb1 
	

	select *,	
	        (select Sum(IBA) from list1) as s_IBA,
	        (select Sum(IBB) from list1) as s_IBB,
	        (select Sum(IBC) from list1) as s_IBC,
	        (select Count(Id) from list1) as c_id	
	from list1
	Where Row Between '+Convert(varchar,@StartIndex)+' and '+Convert(varchar,@EndIndex)+' 
	
	'
	
	-- PRINT (@strSql+@strSql1)
	 EXEC (@strSql+@strSql1)
	
	/*
	(Select Count(Id) as c_id,Sum(IBA) as s_IBA,Sum(IBB) as s_IBB,Sum(IBC) as s_IBC
	From Statistics_Financial
	Where (@CompanyId=0 Or CompanyId=@CompanyId) and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate) as a
	*/
End
	RETURN
